import mysql.connector
from mysql.connector import Error, DatabaseError
import base64
import os
import configparser
import getpass
import logging
import traceback

# 配置日志系统
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger("DatabaseConnector")


class DatabaseConnector:
    _instance = None
    CONFIG_FILE = "db_config.ini"

    def __new__(cls):
        if cls._instance is None:
            cls._instance = super().__new__(cls)
            cls._instance.config = cls._load_or_create_config()
            cls._instance.connect()
        return cls._instance

    @classmethod
    def _load_or_create_config(cls):
        """加载或创建配置文件，增加异常处理"""
        config = configparser.ConfigParser()
        try:
            if not os.path.exists(cls.CONFIG_FILE):
                print("\n首次运行，请设置数据库连接配置:")
                config['database'] = {
                    'host': input("数据库主机地址 (默认localhost): ") or 'localhost',
                    'dbname': input("数据库名称 (默认bank_management_system): ") or 'bank_management_system',
                    'user': input("数据库用户名: ") or 'root'
                }

                # 安全密码处理
                password = getpass.getpass("数据库密码: ")
                config['database']['password'] = base64.b64encode(password.encode()).decode()

                with open(cls.CONFIG_FILE, 'w') as configfile:
                    config.write(configfile)
                logger.info(f"配置文件创建成功: {os.path.abspath(cls.CONFIG_FILE)}")
            else:
                config.read(cls.CONFIG_FILE)
                logger.info("配置文件加载成功")
            return config
        except Exception as e:
            logger.error(f"配置文件处理失败: {str(e)}")
            exit(1)

    def get_decoded_password(self):
        """安全获取解密后的密码"""
        try:
            encoded_pwd = self.config['database']['password']
            return base64.b64decode(encoded_pwd).decode()
        except Exception as e:
            logger.error(f"密码解码失败: {str(e)}")
            exit(1)

    def connect(self):
        try:
            self.connection = mysql.connector.connect(
                host=self.config['database']['host'],
                user=self.config['database']['user'],
                password=self.get_decoded_password(),
                database=self.config['database']['dbname'],
                autocommit=True,
                pool_name="bank_pool",
                pool_size=10,
                pool_reset_session=True
            )

            if self.connection.is_connected():
                logger.info(f"成功连接到数据库: {self.config['database']['dbname']}")

                # === 使用缓冲游标 ===
                cursor = self.connection.cursor(buffered=True)  # 添加buffered=True
                cursor.execute("SELECT 1")
                cursor.fetchall()  # 确保读取所有结果
                cursor.close()
                # ============================

                return self.connection
        except Error as e:
            self._handle_db_error(e)

    def get_cursor(self):
        """获取缓冲游标（解决未读结果问题）"""
        if not self.connection.is_connected():
            logger.warning("连接中断，尝试重新连接")
            self.connect()
        # 创建缓冲游标
        return self.connection.cursor(buffered=True, dictionary=True)

    def _handle_db_error(self, error):
        error_info = {
            'errno': error.errno,
            'sqlstate': error.sqlstate,
            'msg': error.msg,
            'trace': traceback.format_exc()
        }
        logger.critical(f"数据库错误: {error_info}")

        # 特定错误处理
        if error.errno == mysql.connector.errorcode.ER_ACCESS_DENIED_ERROR:
            print("认证失败：请检查用户名和密码")
        elif error.errno == mysql.connector.errorcode.ER_BAD_DB_ERROR:
            print(f"数据库不存在: {self.config['database']['dbname']}")
        else:
            print(f"数据库连接失败: {error.msg}")

        exit(1)

    def get_cursor(self):
        """获取支持字典格式的游标，增加连接检查"""
        if not self.connection.is_connected():
            logger.warning("连接中断，尝试重新连接")
            self.connect()
        return self.connection.cursor(dictionary=True)

    def execute_procedure(self, procedure_name, args):
        """执行存储过程"""
        cursor = None
        try:
            cursor = self.connection.cursor()
            logger.debug(f"执行存储过程: {procedure_name} 参数: {args}")
            cursor.callproc(procedure_name, args)
            self.connection.commit()  # 显式提交
            results = [result.fetchall() for result in cursor.stored_results()]
            return results
        except DatabaseError as e:
            self.connection.rollback()
            logger.error(f"存储过程错误: {procedure_name}|{e.errno}|{e.msg}")
            raise  # 重新抛出异常供上层处理
        finally:
            if cursor: cursor.close()

    def execute_query(self, query, params=None, commit=False):
        cursor = None
        try:
            cursor = self.get_cursor()
            logger.debug(f"执行查询: {query[:100]}... 参数: {params}")
            cursor.execute(query, params or ())

            # 需要提交的写操作
            if commit and not query.strip().upper().startswith("SELECT"):
                self.connection.commit()
                logger.debug("事务已提交")

            return cursor.fetchall()
        except DatabaseError as e:
            # 自动回滚事务
            if self.connection.in_transaction:
                self.connection.rollback()
                logger.warning("事务已回滚")

            # 详细错误信息
            error_detail = {
                'query': query,
                'params': params,
                'errno': e.errno,
                'sqlstate': e.sqlstate,
                'msg': e.msg
            }
            logger.error(f"查询错误: {error_detail}")
            return None
        finally:
            if cursor: cursor.close()

    def commit(self):
        """显式提交事务"""
        try:
            self.connection.commit()
            logger.info("手动提交事务成功")
        except Error as e:
            logger.error(f"提交失败: {e.msg}")
            self.connection.rollback()

    def rollback(self):
        """显式回滚事务"""
        try:
            self.connection.rollback()
            logger.warning("事务已回滚")
        except Error as e:
            logger.error(f"回滚失败: {e.msg}")

    def close(self):
        """安全关闭连接"""
        try:
            if self.connection.is_connected():
                self.connection.close()
                logger.info("数据库连接已安全关闭")
        except Error as e:
            logger.error(f"关闭连接时出错: {e.msg}")

    def check_connection(self):
        """检查连接是否有效"""
        try:
            cursor = self.get_cursor()
            cursor.execute("SELECT 1 AS health_check")
            result = cursor.fetchone()
            cursor.close()
            return result['health_check'] == 1
        except Error:
            return False

